package com.universal.schedule.db;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
/**
 * @author: Saber XIAMEN Intretech
 * @date: 2017/02/23
 * @Description: 数据库管理类
 * */
public class DBManager {
	// 本地Context对象
	private Context mContext = null;
	private static DBManager dbManager = null;
	public static final String DB_NAME = "gtkk.db";// 数据库名称
	private int version = 1;// 数据库版本
	private String databaseName;
	private DatabaseHelper mDatabaseHelper;

	protected DBManager() {
	};

	protected DBManager(Context context) {
		mContext = context;
	}

	public synchronized static DBManager getInstance(Context mContext) {
		if (null == dbManager) {
			dbManager = new DBManager(mContext);
		}
		dbManager.databaseName = DBManager.DB_NAME;

		return dbManager;
	}
	/**
	 * 获取DataBaseHelper
	 * @return
	 */
	public DatabaseHelper getDatabaseHelper(){
		mDatabaseHelper=DatabaseHelper.getInstance(mContext, this.databaseName, null,version);
		return mDatabaseHelper;
	}
	/**
	 * 关闭数据库 注意:当事务成功或者一次性操作完毕时候再关闭
	 */
	public void closeDatabase(SQLiteDatabase dataBase, Cursor cursor) {
		if (null != cursor) {
			cursor.close();
		}
		if (null != dataBase) {
			dataBase.close();
		}
		if (mDatabaseHelper!=null ) {
			mDatabaseHelper.close();
		}
	}
	public void closeDatabase(){
		if (mDatabaseHelper!=null ) {
			mDatabaseHelper.close();
		}
	}
	/**
	 * 执行一条sql语句
	 * @param sql
	 * @param bindArgs
	 */
	public synchronized void execSQL(String sql, Object[] bindArgs) {
		try {
			getDatabaseHelper().getWritableDatabase().execSQL(sql, bindArgs);
		} catch (Exception e) {
			e.printStackTrace();
		} /*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
	}

	/**
	 * 执行一条sql语句
	 *
	 * @param sql
	 */
	public synchronized void execSQL(String sql) {
		try {
			getDatabaseHelper().getWritableDatabase().execSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 向数据库表中插入一条数据
	 *
	 * @param table
	 * @param fieldName
	 * @param values
	 * @return
	 */

	public synchronized long insert(String table,String fieldName, ContentValues values){

		try {
			/* insert方法
			 * 第一个参数：数据库表名;
			 * 第二个参数：如果values为空时则向表中插入一个列名为fieldName,再将该列的值插入null(防止插入空行);
			 * 第三个参数：插入的内容;
			 */
			long insert =  getDatabaseHelper().getWritableDatabase().insert(table, fieldName, values);
			return insert;
		} catch (SQLiteConstraintException e) {
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * 批量删除指定主键数据
	 * @param table 表名
	 * @param mPrimaryKey 主键名
	 * @param primaryKeys 主键值
	 */
	public synchronized  void deleteByPrimaryKey(String table,String mPrimaryKey, Object primaryKeys) {
		try {
			if (primaryKeys.toString().length() > 0) {

				getDatabaseHelper().getWritableDatabase().execSQL("delete from " + table + " where "
								+ mPrimaryKey + " =? ",
						new Object[]{ primaryKeys });
			}
		} catch (Exception e) {
			e.printStackTrace();
		} /*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
	}

	/**
	 * 根据某一字段和值删除一行数据
	 * @param table     表名
	 * @param fieldName 字段名
	 * @param value     字段值
	 * @return 返回值大于0表示删除成功
	 */
	public synchronized int deleteByField(String table, String fieldName, String value) {
		try {
			return  getDatabaseHelper().getWritableDatabase().delete(table, fieldName + " =? ", new String[] { value });
		} catch (Exception e) {
			e.printStackTrace();
		} /*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
		return 0;
	}
	/**
	 * 根据条件删除数据
	 * @param table 表名
	 *
	 * @param whereClause 查询语句 参数采用?
	 * @param whereArgs 参数值
	 * @return 返回值大于0表示删除成功
	 */
	public synchronized int deleteByCondition(String table, String whereClause,String[] whereArgs) {
		try {
			return  getDatabaseHelper().getWritableDatabase().delete(table, whereClause, whereArgs);
		} catch (Exception e) {
			e.printStackTrace();
		} /*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
		return 0;
	}
	/**
	 *  删除表的所有数据
	 */
	public synchronized boolean deleteByTableName(String table) {
		try {
			return  getDatabaseHelper().getWritableDatabase().delete(table, null, null) > 0;
		} catch(Exception ex){
			ex.printStackTrace();
		}/*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
		return false;
	}

	public synchronized int updateTablePrimaryKey(String table,String fileName,int position){
		try {
			getDatabaseHelper().getWritableDatabase().execSQL("update " + table + " set " + fileName + "=" + fileName +"-1" + " where " + fileName + ">? ", new Object[] {position});
		} catch(Exception ex){
			ex.printStackTrace();
			return 0;
		}/*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
		return 1;
	}
	/**
	 * 通过主键，更新某一字段数据
	 * @param table
	 * @param mPrimaryKey1
	 * @param values1
	 * @param fieldName
	 * @param args
	 * @return
	 */
	public synchronized int updateByField(String table,String mPrimaryKey1 ,Object values1,
										  String fieldName , Object args){
		try {
			getDatabaseHelper().getWritableDatabase().execSQL("update " + table + " set "+ fieldName + "=? " + " where "
							+ mPrimaryKey1 + "=? ",
					new Object[] { args,values1});
		} catch (Exception e) {
			//e.printStackTrace();
			return 0;
		} /*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
		return 1;

	}

	/**
	 * 通过联合主键，更新某一字段数据
	 * @param table
	 * @param mPrimaryKey1
	 * @param values1
	 * @param mPrimaryKey2
	 * @param values2
	 * @param fieldName
	 * @param args
	 * @return
	 */
	public synchronized int updateByField(String table,String mPrimaryKey1 ,Object values1,
										  String mPrimaryKey2 ,Object values2,String fieldName , Object args){
		try {
			getDatabaseHelper().getWritableDatabase().execSQL("update " + table + " set "
							+ fieldName + "=? " + " where "
							+ mPrimaryKey1 + "=? and " + mPrimaryKey2 + "=?",
					new Object[] { args,values1, values2 });
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}/* finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
		return 1;

	}
	/**
	 * 根据主键,更新一行数据
	 * @param table       表名
	 * @param mPrimaryKey 字段名
	 * @param args        字段值
	 * @param values
	 * @return 返回值大于0表示更新成功
	 */
	public synchronized int updateById(String table, String mPrimaryKey , String args, ContentValues values) {
		try {
			return  getDatabaseHelper().getWritableDatabase().update(table, values, mPrimaryKey + " =? ",
					new String[] { args });
		} catch (Exception e) {
			e.printStackTrace();
		}/* finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
		return 0;
	}


	/**
	 * 更新一行数据
	 * @param table
	 * @param values
	 * @param whereClause
	 * @param whereArgs
	 * @return 返回值大于0表示更新成功
	 */
	public synchronized int update(String table, ContentValues values, String whereClause,
								   String[] whereArgs) {
		try {
			return  getDatabaseHelper().getWritableDatabase().update(table, values, whereClause, whereArgs);
		} catch (Exception e) {
			e.printStackTrace();
		} /*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
		return 0;
	}

	/**
	 * 使用SQL语句查看某条数据是否存在
	 * @param sql
	 * @param selectionArgs
	 * @return
	 */
	protected synchronized Boolean isExistsBySQL(String sql, String[] selectionArgs) {
		Cursor cursor = null;
		try {
			cursor =  getDatabaseHelper().getReadableDatabase().rawQuery(sql, selectionArgs);
			if (cursor.moveToFirst()) {
				return (cursor.getInt(0) > 0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} /*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,cursor);
			}
		}*/
		return false;
	}
	/**
	 * 根据主键查看某条数据是否存在
	 * @param table
	 * @param value
	 * @return
	 */
	public synchronized Boolean selectByPrimaryKey(String table,String mPrimaryKey, String value) {
		try {
			return selectByField(table, mPrimaryKey, value);
		} catch (Exception e) {
			e.printStackTrace();
		} /*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
		return null;
	}

	/**
	 * 字段获取某字段数据
	 * @param table
	 * @param mPrimaryKey
	 * @param value
	 * @return Cursor
	 */
	public synchronized Cursor selectByFieldKey(String table,String mPrimaryKey, String value){

		Cursor mCursor = null;
		try {
			mCursor =  getDatabaseHelper().getReadableDatabase().rawQuery("select * from " + table
					+ " where " + mPrimaryKey + "= ? " , new String[] { value});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mCursor;
	}

	/**
	 * 根据某字段查询数据库，并进行排序
	 * @param table:表名
	 * @param mPrimaryKey：主键
	 * @param value：主键值
	 * @param mKey：进行排序的键
	 * @return Cursor
	 */
	public synchronized Cursor selectAndOrder(String table,String mPrimaryKey, String value,String mKey){
		Cursor mCursor = null;
		try {
			mCursor =  getDatabaseHelper().getReadableDatabase().rawQuery("select * from " + table
					+ " where " + mPrimaryKey + " = ? order by "+mKey+" asc" , new String[] {value});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mCursor;
	}

	/**
	 * 查询某个表中的数据数目
	 */
	public synchronized int getCount(String table,String mPrimaryKey,String value){
		Cursor mCursor=null;
		try{
			mCursor=getDatabaseHelper().getReadableDatabase().rawQuery("select count(*) from"+table
					+" where "+mPrimaryKey + "=?",new String []{value});
		}catch(Exception e){
			e.printStackTrace();
		}
		return mCursor.getInt(0);
	}

	/**
	 * 在macKey=mac的行中,获取mPrimaryKey模糊匹配value的字段数据
	 * @param table
	 * @param mPrimaryKey
	 * @param value
	 * @param macKey
	 * @param mac
	 * @return
	 */
	public synchronized Cursor selectByLikeKey(String table,
											   String mPrimaryKey, String value, String macKey, String mac) {

		Cursor mCursor = null;
		try {
			mCursor = getDatabaseHelper().getReadableDatabase().rawQuery(
					"select * from " + table + " where " + macKey + " = ? and "
							+ mPrimaryKey + " like ? ",
					new String[] { mac, "%" + value + "%" });
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mCursor;
	}

	/**
	 * 联合字段获取某字段数据
	 * @param table
	 * @param mPrimaryKey1
	 * @param value1
	 * @param mPrimaryKey2
	 * @param value2
	 * @return Cursor
	 */

	public synchronized Cursor selectByField(String table,String mPrimaryKey1, String value1,
											 String mPrimaryKey2, String value2) {
		Cursor mCursor = null;
		try {
			mCursor =  getDatabaseHelper().getReadableDatabase().rawQuery("select * from " + table
					+ " where " + mPrimaryKey1 + "= ? and " + mPrimaryKey2
					+ "= ?", new String[] { value1, value2});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mCursor;
	}

	//获取最后一条数据
	public synchronized Cursor selectLastField(String table){
		Cursor mCursor = null;
		try {
			mCursor=getDatabaseHelper().getReadableDatabase().query(table, null, null, null, null, null, "_id desc ");
//			mCursor =  getDatabaseHelper().getReadableDatabase().rawQuery("select * from " + table
//					+ "order by _id desc limit 0,1",null
//					);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return mCursor;
	}


	/**
	 * 根据某字段/值  查看某条数据是否存在
	 * @return
	 */
	public synchronized Boolean selectByField(String table, String field, String value) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) FROM ").append(table).append(" WHERE ")
				.append(field).append(" =?");
		try {
			return isExistsBySQL(sql.toString(), new String[] { value });
		} catch (Exception e) {
			e.printStackTrace();
		} /*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,null);
			}
		}*/
		return null;
	}
	/**
	 * 查询表的所有数据
	 * @return
	 */
	public synchronized Cursor selectByTableName(String table) {
		return  getDatabaseHelper().getReadableDatabase().rawQuery("select * from " + table, null);
	}

	/**
	 * 重建表
	 * @param sql
	 */
	public  void reCreateTable(String sql) {
		try {
			getDatabaseHelper().getWritableDatabase().execSQL(sql);
		} catch (SQLiteException e) {
			e.printStackTrace();
		}

	}

	/**判断表是否存在
	 * @param table
	 * @return
	 */
	public synchronized boolean tableIsExist(String table) {
		Cursor cursor = null;
		if (table == null) {
			return false;
		}
		try {
			String sql = "select count(*)  from  sqlite_master where type ='table' and name ='"
					+ table + "'";
			cursor =  getDatabaseHelper().getReadableDatabase().rawQuery(sql, null);
			if (cursor.moveToNext()) {
				int count = cursor.getInt(0);
				if (count > 0) {
					return true;
				}
			}
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (SQLiteException e) {
			e.printStackTrace();
		}
		return false;
	}
	/**
	 * 判断表是否有数据
	 * @param table
	 * @return
	 */
	public synchronized boolean tableDataIsNull(String table) {
		Cursor cursor = null;
		if (table==null) {
			return true;
		}
		try {
			String sql = "select *  from " + table;
			cursor =  getDatabaseHelper().getReadableDatabase().rawQuery(sql, null);
			if (cursor.getCount() >0 ) {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}/*finally {
			if (!isTransaction) {
				dBManager.closeDatabase(dataBase,cursor);
			}
		}*/
		return true;
	}
	/**
	 * 删除表
	 */
	public synchronized void dropTable(String table) {
		try {
			getDatabaseHelper().getWritableDatabase().execSQL("DROP TABLE  IF EXISTS "+ table);
		} catch (Exception e) {
			e.printStackTrace();
		} /*finally {
				if (!isTransaction) {
					dBManager.closeDatabase(dataBase,null);
				}
			}*/
	}

	/**
	 * 获取新插入数据自增长的ID值
	 * */
	public synchronized int lastInsertRowid(String table){
		Cursor cursor =  getDatabaseHelper().getReadableDatabase().rawQuery("select last_insert_rowid() from " +table,null);
		int strid = 0;
		if(cursor!=null&&cursor.moveToFirst())
			strid = cursor.getInt(0);
		return strid;
	}

}
